/***********************************************************************************************************************************
 *    Name: Ensure_Notifications.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Ensure that every DBAdmin job is enabled and notifies (DB)A-Team on failure.
**/
USE msdb;
DECLARE @job_id BINARY(16);
DECLARE jobs CURSOR LOCAL FAST_FORWARD FOR SELECT job_id FROM dbo.sysjobs j INNER JOIN dbo.syscategories c ON (j.category_id = c.category_id) WHERE c.name = N'DBAdmin';
OPEN jobs
WHILE (0=0) BEGIN;
	FETCH NEXT FROM jobs INTO @job_id;
	IF @@FETCH_STATUS <> 0 BREAK;
	EXECUTE dbo.sp_update_job
		@job_id						= @job_id,
--		@enabled					= 1,
		@notify_level_page			= 0,	-- Never
		@notify_level_netsend		= 0,	-- Never
		@notify_level_eventlog		= 0,	-- Never
		@notify_level_email			= 2,	-- On Failure
		@notify_email_operator_name	= N'(DB)A-Team';
END;